﻿
using Configuration;
using System;

using System.Data;
using System.Data.SqlClient;

using System.Text;
using System.Threading.Tasks;


namespace Business.Base
{
    public class ExperimentDBManager: IDBManager
    {
        private String selectField = "createTime, code, pumpModel, pumpCode, experimentOwner, manufacturer, pumpType, experimentType,filePath,customer,pumpName";
        private String insertField = "@createTime, @code, @pumpModel, @pumpCode, @experimentOwner, @manufacturer, @pumpType, @experimentType,@filePath,@customer,@pumpName";
        private String table = "experiment";
        SqlConnection connection = null;

        private SqlCommand sqlSelectCountCommand = null;
        private SqlCommand sqlSelectCommand = null;
        private SqlDataAdapter sqlSelectAdapter = null;
        private SqlCommand sqlInsertCommand = null;
        private SqlCommand sqlDeleteCommand = null;
        private SqlCommand sqlUpdateCommand =null;

        public ExperimentDBManager()
        {            
            String connectionString = DataSourceConfig.DataSourceString;
            connection = new SqlConnection(connectionString);
            
            sqlSelectCountCommand = new SqlCommand();
            sqlSelectCountCommand.Connection = connection;


            sqlInsertCommand = new SqlCommand();
            sqlInsertCommand.Connection = this.connection;
            sqlInsertCommand.CommandText = "INSERT INTO ["+table+"] ("+ selectField + ") VALUES ("+ insertField + ") SELECT IDENT_CURRENT('"+table+"')";

            sqlDeleteCommand = new SqlCommand();
            sqlDeleteCommand.Connection = this.connection;
            sqlDeleteCommand.CommandText = "delete from " + table ;

            sqlUpdateCommand = new SqlCommand();
            sqlUpdateCommand.Connection = this.connection;

            sqlSelectAdapter = new SqlDataAdapter();
            sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.Connection = connection;
            sqlSelectAdapter.SelectCommand = sqlSelectCommand;
            
        }

        private String getPageQuery(ExperimentQuery query)
        {
            String pageQuery = " offset " + (query.PageNum - 1) * query.PageSize + " rows fetch next " + query.PageSize + " rows only;";
            //tring pageQuery = " limit " + query.PageSize + " offset " + (query.PageNum - 1) * query.PageSize;
            return pageQuery;
        }


        private String buildCondition(ExperimentQuery query, Boolean addOrder)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" where ");
            sb.Append(" (createTime > '" + query.CreateStartTime.ToString() + "' and createTime <'" + query.CreateEndTime.ToString() + "')");

            if (query.Code != null && query.Code.Trim().Length!=0)
            {
                sb.Append(" and  (code like '%" +query.Code+"%') ");
            }
            if (query.PumpName != null && query.PumpName.Length != 0)
            {
                sb.Append(" and  (pumpName like '%" + query.PumpName + "%') ");
            }
            if (query.Customer != null && query.Customer.Length != 0)
            {
                sb.Append(" and  (customer like '%" + query.Customer + "%') ");
            }
            if (query.ExperimentOwner!=null && query.ExperimentOwner.Length!=0)
            {
                sb.Append(" and  (experimentOwner like '%" + query.ExperimentOwner + "%') ");
            }
            if (query.Manufacturer != null && query.Manufacturer.Trim().Length!=0)
            {
                sb.Append(" and  (manufacturer like '%" + query.Manufacturer + "%') ");
            }
            if (query.PumpCode != null && query.PumpCode.Trim().Length!=0)
            {
                sb.Append(" and  (pumpCode like '%" + query.PumpCode + "%') ");
            }
            if (query.PumpModel != null && query.PumpModel.Trim().Length!=0)
            {
                sb.Append(" and  (pumpModel like '%" + query.PumpModel + "%') ");
            }
            if (query.PumpType!=null&&query.PumpType.Length != 0)
            {
                sb.Append(" and  (pumpType in (");
                foreach (int v in query.PumpType)
                {
                    sb.Append(v.ToString()+",");
                }
                sb.Append("0)) ");                
            }
            if (query.ExperimentType!=null&&query.ExperimentType.Length != 0)
            {
                sb.Append(" and  (experimentType in (");
                foreach (int v in query.ExperimentType)
                {
                    sb.Append(v.ToString() + ",");
                }
                sb.Append("0)) ");
            }

            if (addOrder&&query.OrderBy != null)
            {
                sb.Append(" order by " + query.OrderBy);
                sb.Append(" " + query.OrderType);
            }
            return sb.ToString();
        }

       
        
        public int GetCount(BaseQuery query)
        {
            sqlSelectCountCommand.CommandText = "select count(*) from " + table + this.buildCondition((ExperimentQuery)query,false);
            connection.Open();
            object result = sqlSelectCountCommand.ExecuteScalar();
            connection.Close();
            return (int)result;     

        }

        public DataTable GetDataList(BaseQuery query)
        {
            sqlSelectCommand.CommandText = "select "+this.selectField+" from " + this.table + this.buildCondition((ExperimentQuery)query,true) + this.getPageQuery((ExperimentQuery)query);
            DataTable dataTable = new DataTable();
            sqlSelectAdapter.Fill(dataTable);
            return dataTable;
        }

        public int Insert(Object obj)
        {
            Experiment experiment = (Experiment)obj;
            sqlInsertCommand.Parameters.Clear();
            sqlInsertCommand.Parameters.Add(new SqlParameter("@createTime", DateTime.Now));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@code", experiment.Code));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@customer", experiment.Customer));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@pumpName", experiment.PumpName));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@pumpModel", experiment.PumpModel));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@pumpCode", experiment.PumpCode));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentOwner", experiment.ExperimentOwner));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@manufacturer", experiment.Manufacturer));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@pumpType", experiment.PumpType));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@experimentType", experiment.ExperimentType));
            sqlInsertCommand.Parameters.Add(new SqlParameter("@filePath", experiment.FilePath==null?"": experiment.FilePath));
            connection.Open();
            int id=  (int)sqlInsertCommand.ExecuteScalar();
            //sqlInsertCommand.ExecuteNonQuery();
            connection.Close();
            return id;
        }

        public void UpdateFilePath(String code, String filePath)
        {
            sqlUpdateCommand.CommandText = "update experiment set filePath = @filePath where code ='" + code+"'";
            sqlUpdateCommand.Parameters.Clear();
            sqlUpdateCommand.Parameters.Add(new SqlParameter("@filePath", filePath));
            connection.Open();
            sqlUpdateCommand.ExecuteNonQuery();
            connection.Close();
        }

        public void Delete(Object code)
        {
            if (code != null)
            {
                sqlDeleteCommand.CommandText += " where code = @code";
                sqlDeleteCommand.Parameters.Add(new SqlParameter("@code", (String)code));
            }
            
            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            connection.Close();
        }

        public void DeleteByCondtion(string condition)

        {
            if (condition != null)
            {
                sqlDeleteCommand.CommandText += condition;  
            }

            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            connection.Close();

        }
    }
}
